package com.hyperzsoft.budgeteer;

import java.util.ArrayList;

import com.hyperzsoft.budget.R;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class BudgetProvider {
	OpenHelper openHelper = null;
	
	private static final String DATABASE_NAME = "onabudget.db";
	private static final int DATABASE_VERSION = 2;
	private static final String TAG = "BudgetProvider";
	
	private static final String EXPENSE_GROUP_TABLE_NAME = "ExpenseGroup";
	private static final String EXPENSE_TABLE_NAME = "Expense";
	private static final String EXPENSE_TYPE_TABLE_NAME = "ExpenseType";
	private static final String PERIOD_TABLE_NAME = "Period";
	
	private static SQLiteDatabase budgetDB;
	
	private Context context;
	private SQLiteStatement insertStmt;
	
	public enum BudgetTable {
		EXPENSE_GROUP (EXPENSE_GROUP_TABLE_NAME),
		EXPENSE (EXPENSE_TABLE_NAME),
		EXPENSE_TYPE (EXPENSE_TYPE_TABLE_NAME),
		PERIOD (PERIOD_TABLE_NAME);
		
		private BudgetTable(String aTable) {
			//can't call super ctor here
			fTable = aTable;
		}
		
		final String getTable() {
			return fTable;
		}

		private final String fTable;
	}
	
	public BudgetProvider(Context context) {
		try {
			this.context = context;
			openHelper = new OpenHelper(this.context);
			budgetDB = openHelper.getWritableDatabase();
			
			initGlobals();
			
			
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			Log.e(TAG, e.getStackTrace().toString());
		}
	}
	
	public void close() {
	    if (openHelper != null) {
	        openHelper.close();
	    }
	}
	
	private void initGlobals() {
		
	}
	
	public long insert(Expense expense) {
		try {
			this.insertStmt = budgetDB.compileStatement("INSERT INTO " + EXPENSE_TABLE_NAME + " ("
				+ this.context.getString(R.string.expense_name) + ","
				+ this.context.getString(R.string.expense_group) + ","
				+ this.context.getString(R.string.expense_type) + ","
				+ this.context.getString(R.string.expense_period)  + ","
				+ this.context.getString(R.string.expense_amount) + ")"
				+ " VALUES (?,?,?,?,?)");
			
			this.insertStmt.bindString(1, expense.NAME);
			this.insertStmt.bindLong(2, expense.GROUP_ID);
			this.insertStmt.bindLong(3, expense.TYPE_ID);
			this.insertStmt.bindLong(4, expense.PERIOD_ID);
			this.insertStmt.bindLong(5, (long) expense.AMOUNT_PAID);
			
			return this.insertStmt.executeInsert();
			
		} catch (SQLException e) {
			Log.e(TAG, e.getLocalizedMessage());
			Log.e(TAG, e.getStackTrace().toString());
			return -1;
		}
	}
	
	public long insert(ExpenseGroup expenseGrp) {
		try {
			this.insertStmt = budgetDB.compileStatement("INSERT INTO " + EXPENSE_GROUP_TABLE_NAME + " ("
				+ this.context.getString(R.string.expense_grp_name) + ","
				+ this.context.getString(R.string.expense_grp_color) + ")"
				+ " VALUES (?,?)");

			this.insertStmt.bindString(1, expenseGrp.GROUP_NAME);
			this.insertStmt.bindString(2, expenseGrp.COLOR);
			
			return this.insertStmt.executeInsert();
		} catch (SQLException e) {
			Log.e(TAG, e.getLocalizedMessage());
			Log.e(TAG, e.getStackTrace().toString());
			return -1;
		}
	}
	
	public long insert(ExpenseType expenseType) {
		try {
			this.insertStmt = budgetDB.compileStatement("INSERT INTO " + EXPENSE_TYPE_TABLE_NAME + " ("
					+ this.context.getString(R.string.expense_type_name) + ","
					+ this.context.getString(R.string.expense_type_type) + ","
					+ this.context.getString(R.string.expense_type_amount) + ","
					+ this.context.getString(R.string.expense_type_color) + ")"
					+ " VALUES (?,?,?,?)");

			this.insertStmt.bindString(1, expenseType.EXPENSE_NAME);
			this.insertStmt.bindString(2, expenseType.TYPE);
			this.insertStmt.bindLong(3, (long) expenseType.ALLOWED_AMOUNT);
			this.insertStmt.bindString(4, expenseType.COLOR);
			
			return this.insertStmt.executeInsert();
		} catch (SQLException e) {
			Log.e(TAG, e.getLocalizedMessage());
			Log.e(TAG, e.getStackTrace().toString());
			return -1;
		}
	}
	
	public long insert(Period period) {
		try {
			this.insertStmt = budgetDB.compileStatement("INSERT INTO " + PERIOD_TABLE_NAME + " ("
					+ this.context.getString(R.string.period_date) + ","
					+ this.context.getString(R.string.period_label) + ","
					+ this.context.getString(R.string.period_earnings) + ","
					+ this.context.getString(R.string.period_start_amount) + ","
					+ this.context.getString(R.string.period_end_amount) + ","
					+ this.context.getString(R.string.period_total_paid) + ")"
					+ " VALUES (?,?,?,?,?,?)");

			this.insertStmt.bindLong(1, period.DATE);
			this.insertStmt.bindString(2, period.LABEL);
			this.insertStmt.bindLong(3, (long) period.EARNINGS);
			this.insertStmt.bindLong(4, (long) period.START_AMOUNT);
			this.insertStmt.bindLong(5, (long) period.END_AMOUNT);
			this.insertStmt.bindLong(6, (long) period.TOTAL_PAID);
			
			return this.insertStmt.executeInsert();
		} catch (SQLException e) {
			Log.e(TAG, e.getLocalizedMessage());
			Log.e(TAG, e.getStackTrace().toString());
			return -1;
		}
	}
	
	public void delete(BudgetTable db_table, int rowID) { 
		budgetDB.delete(db_table.getTable(), "_id=" + rowID, null);
	}
	
	public void deleteAll(BudgetTable db_table) {
		budgetDB.delete(db_table.getTable(), null, null);
	}
	
	public ArrayList<Object> loadObjectList() {
		ArrayList<Object> list = new ArrayList<Object>();
		
		try {
//			if (cursor.moveToFirst()) {
//				do {
//					switch (db_table) {
//					case EXPENSE:
//						expenseObj = new Expense();
//						expenseObj._ID = (int) cursor.getLong(0);
//						expenseObj.NAME = cursor.getString(1);
//						expenseObj.TYPE = cursor.getString(2);
//						expenseObj.AMOUNT = (double) cursor.getLong(3);
//						expenseObj.AMOUNT_TYPE = cursor.getString(4);
//
//						list.add(expenseObj);
//						break;
//					case CREDIT:
//						creditObj = new Credit();
//						creditObj._ID = (int) cursor.getLong(0);
//						creditObj.CREDIT_DATE = cursor.getLong(2);
//						creditObj.AMOUNT = cursor.getLong(1);
//
//						list.add(creditObj);
//						break;
//					case MONTHLY:
//						monthlyObj = new Monthly();
//						monthlyObj._ID = (int) cursor.getLong(0);
//						monthlyObj.EXPENSE_ID = (int) cursor.getLong(1);
//						monthlyObj.CREDIT_ID = (int) cursor.getLong(2);
//						monthlyObj.DUE_DATE = cursor.getLong(3);
//						monthlyObj.AMOUNT_PAID = (double) cursor.getLong(4);
//						monthlyObj.BALANCE = (double) cursor.getLong(5);
//
//						list.add(monthlyObj);
//						break;
//					default:
//						throw new IllegalArgumentException("Invalid or NULL Database Table");
//					}
//				} while (cursor.moveToNext());
//			}
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			Log.e(TAG, e.getStackTrace().toString());
		}
		
		return list;
	}
	
	public ArrayList<Object> selectAll() {
//		Cursor cursor;
//		String[] table_cols;
		ArrayList<Object> returnList = new ArrayList<Object>();
		
		try {
//			switch (db_table) {
//				case EXPENSE:
//					table_cols = EXPENSE_SELECT_COLS;
//					break;
//				case CREDIT:
//					table_cols = CREDIT_SELECT_COLS;
//					break;
//				case MONTHLY:
//					table_cols = MONTHLY_SELECT_COLS;
//					break;
//				default:
//					throw new IllegalArgumentException("Invalid or NULL Database Table");
//			}
			
			cursor = budgetDB.query(db_table.getTable(), table_cols, 
					null, null, null, null, db_table.getSort());
//			
//			ArrayList<Object> list = loadObjectList(db_table, cursor);
//			
//			if (cursor != null && !cursor.isClosed()) {
//				cursor.close();
//			}
//			
			//damn scope, again a better way to do this? I need to return 
			// that list but different items need to be set to load the list.
//			returnList = list;
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			Log.e(TAG, e.getStackTrace().toString());
		}
		
		return returnList;
	}
	
	@SuppressWarnings("unused")
	public class OpenHelper extends SQLiteOpenHelper {
		private static final String TAG = "OpenHelper";
		private Context context;

		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			this.context = context;
		}

		@Override
		public void onCreate(SQLiteDatabase budgetDB) {
			//Create all the tables
			budgetDB.execSQL("CREATE TABLE " + EXPENSE_TABLE_NAME + " ("
					+ "_id INTEGER PRIMARY KEY,"
					+ this.context.getString(R.string.expense_name) + " TEXT,"
					+ this.context.getString(R.string.expense_group) + " INTEGER,"
					+ this.context.getString(R.string.expense_type) + " INTEGER,"
					+ this.context.getString(R.string.expense_period) + " INTEGER,"
					+ this.context.getString(R.string.expense_amount) + " REAL"
					+ ");");

			budgetDB.execSQL("CREATE TABLE " + EXPENSE_GROUP_TABLE_NAME + " ("
					+ "_id INTEGER PRIMARY KEY,"
					+ this.context.getString(R.string.expense_grp_name) + " TEXT,"
					+ this.context.getString(R.string.expense_grp_color) + " TEXT"
					+ ");");
			
			budgetDB.execSQL("CREATE TABLE " + EXPENSE_TYPE_TABLE_NAME + " ("
					+ "_id INTEGER PRIMARY KEY,"
					+ this.context.getString(R.string.expense_type_name) + " TEXT,"
					+ this.context.getString(R.string.expense_type_type) + " TEXT,"
					+ this.context.getString(R.string.expense_type_amount) + " REAL,"
					+ this.context.getString(R.string.expense_type_color) + " TEXT"
					+ ");");
			
			budgetDB.execSQL("CREATE TABLE " + PERIOD_TABLE_NAME + " ("
					+ "_id INTEGER PRIMARY KEY,"
					+ this.context.getString(R.string.period_date) + " INTEGER,"
					+ this.context.getString(R.string.period_label) + " TEXT,"
					+ this.context.getString(R.string.period_earnings) + " REAL,"
					+ this.context.getString(R.string.period_start_amount) + " REAL,"
					+ this.context.getString(R.string.period_end_amount) + " REAL,"
					+ this.context.getString(R.string.period_total_paid) + " REAL"
					+ ");");
		}

		@Override
		public void onUpgrade(SQLiteDatabase budgetDB, int oldVersion, int newVersion) {
			budgetDB.execSQL("DROP TABLE IF EXISTS " + EXPENSE_TABLE_NAME);
			budgetDB.execSQL("DROP TABLE IF EXISTS " + EXPENSE_GROUP_TABLE_NAME);
			budgetDB.execSQL("DROP TABLE IF EXISTS " + EXPENSE_TYPE_TABLE_NAME);
			budgetDB.execSQL("DROP TABLE IF EXISTS " + PERIOD_TABLE_NAME);
			
			onCreate(budgetDB);
		}
	}
	
	public static final class Expense {
		public long _ID;
		public String NAME;
		public long GROUP_ID;
		public long TYPE_ID;
		public long PERIOD_ID;
		public double AMOUNT_PAID;
		
		public long insert() {
			try {
				this.insertStmt = budgetDB.compileStatement("INSERT INTO " + EXPENSE_TABLE_NAME + " ("
					+ this.context.getString(R.string.expense_name) + ","
					+ this.context.getString(R.string.expense_group) + ","
					+ this.context.getString(R.string.expense_type) + ","
					+ this.context.getString(R.string.expense_period)  + ","
					+ this.context.getString(R.string.expense_amount) + ")"
					+ " VALUES (?,?,?,?,?)");
				
				this.insertStmt.bindString(1, expense.NAME);
				this.insertStmt.bindLong(2, expense.GROUP_ID);
				this.insertStmt.bindLong(3, expense.TYPE_ID);
				this.insertStmt.bindLong(4, expense.PERIOD_ID);
				this.insertStmt.bindLong(5, (long) expense.AMOUNT_PAID);
				
				return this.insertStmt.executeInsert();
				
			} catch (SQLException e) {
				Log.e(TAG, e.getLocalizedMessage());
				Log.e(TAG, e.getStackTrace().toString());
				return -1;
			}
		}
	}
	
	public static final class ExpenseGroup {
		public long _ID;
		public String GROUP_NAME;
		public String COLOR;
	}
	
	public static final class ExpenseType {
		public long _ID;
		public String EXPENSE_NAME;
		public String COLOR;
		public String TYPE;
		public double ALLOWED_AMOUNT;
	}
	
	public static final class Period {
		public long _ID;
		public String LABEL;
		public long DATE;
		public double EARNINGS;
		public double START_AMOUNT;
		public double END_AMOUNT;
		public double TOTAL_PAID;
	}
}
